package com.wechat.third;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.wechat.third.util.FastJsonUtils;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.io.*;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @author
 * @date 2018/12/12
 */
public class GenerateSetApplySellOIDSQl {

	@Test
	public void setApplySellOIDByChannel() {
		try {
			//1、创建流对象
			Reader reader = new FileReader("D:/zbf.xlsx");
			//构建高效流对象
			BufferedReader buffReader = new BufferedReader(reader);

			//2、读取一行字符串
			String line = buffReader.readLine();
			StringBuffer buffer = new StringBuffer();
			while (line != null) {
				String[] str = line.split("\t");
				String sql = "update zbf_user.zb_apply set seller_oid=1 where id=" + str[1].toString() + ";";
				buffer.append(sql + "\r\n");
				line = buffReader.readLine();
			}
			System.out.println(buffer.toString());
			;
			//3、关闭流
			buffReader.close();
			reader.close();

			Writer w = new FileWriter("D:/newZbf.txt");
			BufferedWriter buffWriter = new BufferedWriter(w);

			buffWriter.write(buffer.toString());

			buffWriter.close();
			w.close();
			System.out.println("写入成功！");

		} catch (FileNotFoundException e) {
			System.out.println("要读取的文件不存在：" + e.getMessage());
		} catch (IOException e) {
			System.out.println("文件读取错误：" + e.getMessage());


		}

	}

	@Test
	public void setApplySellOIDByChannelExcel() {
		String filepath = "d:\\prod.xls";
		try {
			Workbook workbook = Workbook.getWorkbook(new File(filepath));
			Sheet sheet = workbook.getSheet(0);
			File file = new File("d:/prod.txt");
			FileWriter fw = new FileWriter(file);
			BufferedWriter bw = new BufferedWriter(fw);
			// j为行数，getCell("列号","行号")
			int j = sheet.getRows();
			int y = sheet.getColumns();
			for (int i = 0; i < j; i++) {
				/*String statusCode = JSONObject.parseObject(sheet.getCell(0, i).getContents()).get("statusCode").toString();
				if (StringUtils.isNotBlank(statusCode) && !statusCode.equals("200")) {
					continue;
				}*/
				String extData=sheet.getCell(0, i).getContents();
				String sellerOID = null;
				if(extData.contains("sellerOID")){
					sellerOID = JSONObject.parseObject(extData).getJSONObject("result").getJSONObject("data").get("sellerOID").toString();
				}
				if (StringUtils.isBlank(sellerOID)) {
					if(JSONObject.parseObject(extData).get("data")==null){
						continue;
					}
					sellerOID = JSONObject.parseObject(extData).get("data").toString();
				}
				String sql = "update zbf_user.zb_apply set seller_oid='" + sellerOID + "' where id=" + sheet.getCell(1, i).getContents() + ";";
				bw.write(sql);
				bw.write(" ");
				bw.flush();

				bw.newLine();
				bw.flush();
			}
			System.out.println("写入结束");
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}


}
